/*
  Copyright 2009 Smart Enterprise Solution Corp.
  Email: contact@ses.vn - Website: http://www.ses.vn
  KimNgan Project.
*/
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Data.SqlClient;
using DOAN.DO;
/// <summary>
/// Summary description for CauhoiDAL
/// </summary>
namespace DOAN.DAL
{

    public class CauhoiDAL : BaseDAL
    {
        #region Private Variables

        #endregion

        #region Public Constructors
        public CauhoiDAL()
        {
            //
            // TODO: Add constructor logic here
            //

        }
        #endregion



        #region Public Methods
        public int Insert(CauhoiDO objCauhoiDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_Insert";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Mamonhoc", SqlDbType.VarChar);
            Sqlparam.Value = objCauhoiDO.Mamonhoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Noidung", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Noidung;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Dokho", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Dokho;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Chuong", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Chuong;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Image", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Image;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Video", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Video;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanA", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanA;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanB", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanB;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanC", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanC;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanD", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanD;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Phuongandung", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Phuongandung;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@ID", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);


            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ID"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ID"].Value);

            return result;
        }

        public DataTable TaoDe(string mon, int kho, int tb, int de)
        {
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_Taode";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@mon", SqlDbType.VarChar);
            Sqlparam.Value = mon;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@kho", SqlDbType.Int);
            Sqlparam.Value = kho;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@tb", SqlDbType.Int);
            Sqlparam.Value = tb;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@de", SqlDbType.Int);
            Sqlparam.Value = de;
            Sqlcomm.Parameters.Add(Sqlparam);

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];
            }

            return dt;
        }

        public int Update(CauhoiDO objCauhoiDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_UpdateByPK";
            SqlParameter Sqlparam;

            Sqlparam = new SqlParameter("@Macauhoi", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Macauhoi;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Mamonhoc", SqlDbType.VarChar);
            Sqlparam.Value = objCauhoiDO.Mamonhoc;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Noidung", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Noidung;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Dokho", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Dokho;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Chuong", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Chuong;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Image", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Image;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Video", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Video;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanA", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanA;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanB", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanB;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanC", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanC;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@DapanD", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.DapanD;
            Sqlcomm.Parameters.Add(Sqlparam);

            Sqlparam = new SqlParameter("@Phuongandung", SqlDbType.NVarChar);
            Sqlparam.Value = objCauhoiDO.Phuongandung;
            Sqlcomm.Parameters.Add(Sqlparam);



            Sqlparam = new SqlParameter("@ErrorCode", SqlDbType.Int);
            Sqlparam.Direction = ParameterDirection.ReturnValue;
            Sqlcomm.Parameters.Add(Sqlparam);

            int result = base.ExecuteNoneQuery(Sqlcomm);

            if (!Convert.IsDBNull(Sqlcomm.Parameters["@ErrorCode"]))
                result = Convert.ToInt32(Sqlcomm.Parameters["@ErrorCode"].Value);

            return result;


        }

        public int Delete(CauhoiDO objCauhoiDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_DeleteByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Macauhoi", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Macauhoi;
            Sqlcomm.Parameters.Add(Sqlparam);



            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public int DeleteAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_DeleteAll";

            int result = base.ExecuteNoneQuery(Sqlcomm);
            return result;
        }

        public DataTable Timkiem(string tk)
        {
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_Timkiem";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@tk", SqlDbType.NVarChar);
            Sqlparam.Value = tk;
            Sqlcomm.Parameters.Add(Sqlparam);



            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                
                dt = ds.Tables[0];
            }

            return dt;
        }

        public DataTable GetByMon(string s)
        {
            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauHoi_GetByMon";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Mamon", SqlDbType.VarChar);
            Sqlparam.Value = s;
            Sqlcomm.Parameters.Add(Sqlparam);



            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];
            }

            return dt;
        }

        public DataTable GetCH(string s)
        {
            string[] separator = new string[] { "-" };

            string[] kq = s.Split(separator, StringSplitOptions.RemoveEmptyEntries);
            DataTable data = new DataTable();
            int i = 1;
            foreach (string ma in kq)
            {
                SqlCommand Sqlcomm = new SqlCommand();
                Sqlcomm.CommandType = CommandType.StoredProcedure;
                Sqlcomm.CommandText = "spCauhoi_GetByPK";
                SqlParameter Sqlparam;


                Sqlparam = new SqlParameter("@Macauhoi", SqlDbType.Int);
                Sqlparam.Value = int.Parse(ma);
                Sqlcomm.Parameters.Add(Sqlparam);
                
                DataSet ds = base.GetDataSet(Sqlcomm);
                DataTable dt = new DataTable();
                if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                {
                    dt = ds.Tables[0];
                    dt.Columns.Add("STT");
                    dt.Rows[0].ItemArray[11] = i;
                    i++;
                }
                data.Merge(dt);

                //DataRow dr = null;
                //if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                //{
                //    dr = ds.Tables[0].Rows[0];
                //}
                //data.ImportRow(dr);
            }
            return data;
        }

        public DataTable Select2(CauhoiDO objCauhoiDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_GetByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Macauhoi", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Macauhoi;
            Sqlcomm.Parameters.Add(Sqlparam);



            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = new DataTable();
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dt = ds.Tables[0];
            }
            return dt;
        }

        public CauhoiDO Select(CauhoiDO objCauhoiDO)
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_GetByPK";
            SqlParameter Sqlparam;


            Sqlparam = new SqlParameter("@Macauhoi", SqlDbType.Int);
            Sqlparam.Value = objCauhoiDO.Macauhoi;
            Sqlcomm.Parameters.Add(Sqlparam);



            DataSet ds = base.GetDataSet(Sqlcomm);
            DataRow dr = null;
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
            {
                dr = ds.Tables[0].Rows[0];
                if (!Convert.IsDBNull(dr["Macauhoi"]))
                    objCauhoiDO.Macauhoi = Convert.ToInt32(dr["Macauhoi"]);
                if (!Convert.IsDBNull(dr["Mamonhoc"]))
                    objCauhoiDO.Mamonhoc = Convert.ToString(dr["Mamonhoc"]);
                if (!Convert.IsDBNull(dr["Noidung"]))
                    objCauhoiDO.Noidung = Convert.ToString(dr["Noidung"]);
                if (!Convert.IsDBNull(dr["Dokho"]))
                    objCauhoiDO.Dokho = Convert.ToInt32(dr["Dokho"]);
                if (!Convert.IsDBNull(dr["Chuong"]))
                    objCauhoiDO.Chuong = Convert.ToInt32(dr["Chuong"]);
                if (!Convert.IsDBNull(dr["Image"]))
                    objCauhoiDO.Image = Convert.ToString(dr["Image"]);
                if (!Convert.IsDBNull(dr["Video"]))
                    objCauhoiDO.Video = Convert.ToString(dr["Video"]);
                if (!Convert.IsDBNull(dr["DapanA"]))
                    objCauhoiDO.DapanA = Convert.ToString(dr["DapanA"]);
                if (!Convert.IsDBNull(dr["DapanB"]))
                    objCauhoiDO.DapanB = Convert.ToString(dr["DapanB"]);
                if (!Convert.IsDBNull(dr["DapanC"]))
                    objCauhoiDO.DapanC = Convert.ToString(dr["DapanC"]);
                if (!Convert.IsDBNull(dr["DapanD"]))
                    objCauhoiDO.DapanD = Convert.ToString(dr["DapanD"]);
                if (!Convert.IsDBNull(dr["Phuongandung"]))
                    objCauhoiDO.Phuongandung = Convert.ToString(dr["Phuongandung"]);

            }
            return objCauhoiDO;
        }

        public ArrayList SelectAll1()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;
            ArrayList arrCauhoiDO = new ArrayList();
            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    CauhoiDO objCauhoiDO = new CauhoiDO();
                    if (!Convert.IsDBNull(dr["Macauhoi"]))
                        objCauhoiDO.Macauhoi = Convert.ToInt32(dr["Macauhoi"]);
                    if (!Convert.IsDBNull(dr["Mamonhoc"]))
                        objCauhoiDO.Mamonhoc = Convert.ToString(dr["Mamonhoc"]);
                    if (!Convert.IsDBNull(dr["Noidung"]))
                        objCauhoiDO.Noidung = Convert.ToString(dr["Noidung"]);
                    if (!Convert.IsDBNull(dr["Dokho"]))
                        objCauhoiDO.Dokho = Convert.ToInt32(dr["Dokho"]);
                    if (!Convert.IsDBNull(dr["Chuong"]))
                        objCauhoiDO.Chuong = Convert.ToInt32(dr["Chuong"]);
                    if (!Convert.IsDBNull(dr["Image"]))
                        objCauhoiDO.Image = Convert.ToString(dr["Image"]);
                    if (!Convert.IsDBNull(dr["Video"]))
                        objCauhoiDO.Video = Convert.ToString(dr["Video"]);
                    if (!Convert.IsDBNull(dr["DapanA"]))
                        objCauhoiDO.DapanA = Convert.ToString(dr["DapanA"]);
                    if (!Convert.IsDBNull(dr["DapanB"]))
                        objCauhoiDO.DapanB = Convert.ToString(dr["DapanB"]);
                    if (!Convert.IsDBNull(dr["DapanC"]))
                        objCauhoiDO.DapanC = Convert.ToString(dr["DapanC"]);
                    if (!Convert.IsDBNull(dr["DapanD"]))
                        objCauhoiDO.DapanD = Convert.ToString(dr["DapanD"]);
                    if (!Convert.IsDBNull(dr["Phuongandung"]))
                        objCauhoiDO.Phuongandung = Convert.ToString(dr["Phuongandung"]);
                    arrCauhoiDO.Add(objCauhoiDO);
                }
            }
            return arrCauhoiDO;
        }

        public DataTable SelectAll()
        {

            SqlCommand Sqlcomm = new SqlCommand();
            Sqlcomm.CommandType = CommandType.StoredProcedure;
            Sqlcomm.CommandText = "spCauhoi_GetAll";

            DataSet ds = base.GetDataSet(Sqlcomm);
            DataTable dt = null;

            if (ds != null && ds.Tables.Count > 0)
            {
                dt = ds.Tables[0];

            }
            return dt;
        }


        #endregion

    }

}
